##################################################################################################################
# Code for Results in Appendix Section A6
##################################################################################################################
write.table(data.frame(Description=c("","","","Appendix Section A6: Evidence on Differences in Lobbying Activities Between Contract and In-House Lobbyists")),row.names=F,col.names=F,quote=F)

##################################################################################################################
# To run, please set the working directory to match the location of the folder containing all replication files.
##################################################################################################################
#setwd("Replication_PSRM_ST24")

#if not yet created, create "output" folder
if("output"%in%list.files()==F){dir.create("output")}

#Clear environment
rm(list=ls());gc();gc();gc();gc()

#If not yet installed, install packages
if(length(find.package("dplyr",quiet=T))==0){install.packages("dplyr")}
if(length(find.package("knitr",quiet=T))==0){install.packages("knitr")} 
if(as.numeric(available.packages()["knitr","Version"])<1.48){install.packages("knitr")} #updated version required to prevent error with kbl(x,format="latex)
if(length(find.package("kableExtra",quiet=T))==0){install.packages("kableExtra")}
if(length(find.package("stargazer",quiet=T))==0){install.packages("stargazer")}

#Load package
library(dplyr)
library(kableExtra)
library(stargazer)

#function: winsorization
winsor1<-function (x, fraction=.05)
{
  if(length(fraction) != 1 || fraction < 0 ||
     fraction > 0.5) {
    stop("bad value for 'fraction'")
  }
  lim <- quantile(x, probs=c(fraction, 1-fraction),na.rm=T)
  x[ x < lim[1] ] <- lim[1]
  x[ x > lim[2] ] <- lim[2]
  x
}

#Set POSIX locale
Sys.setlocale(locale="C")

#Load main data set
loball5ciA<-readRDS("Data/PSRM_main_dataset.rds")


loball5ciA_la1<-loball5ciA %>%
  filter(Rcomparable_total_amounts_cleaned_exp>0) %>%
  group_by(lobname_cleaned,principal_cleaned,session,type) %>%
  summarise(.groups="keep",hours=sum(hours),communicating=sum(communicating),other=sum(other),cprop=sum(communicating)/sum(hours))


write.table(data.frame(Description=c("","Results:")),row.names=F,col.names=F,quote=F)
write.table(data.frame(Description=c("","Figure A7: Proportion of Time Worked and Total Hours Worked by Communicating with Public
Officials, Client-Lobbyist Level")),row.names=F,col.names=F,quote=F)

png("output/PSRM_figA7.png",width=8,height = 4,units="in",res=300)
  par(mfrow=c(1,2),mar=c(3,3,3,3))
  #Panel 1
  boxplot(main="(1) Proportion of Time Worked\nSpent Communicating",loball5ciA_la1$cprop[loball5ciA_la1$type=="contract"],
          loball5ciA_la1$cprop[loball5ciA_la1$type=="inhouse"],names=c("Contract","In-House"))
  #Panel 2
  boxplot(main="(2) Hours Spent Communicating",winsor1(loball5ciA_la1$hours[loball5ciA_la1$type=="contract"]*loball5ciA_la1$cprop[loball5ciA_la1$type=="contract"]),
          winsor1(loball5ciA_la1$hours[loball5ciA_la1$type=="inhouse"]*loball5ciA_la1$cprop[loball5ciA_la1$type=="inhouse"]),names=c("Contract","In-House"))
dev.off()

write.table(data.frame(Description=c("(N.B. The file for this figure is saved as 'PSRM_figA7.png' in the 'output' folder.)")),row.names=F,col.names=F,quote=F)


#Include code again so that Figure A7 also shown in R if code file is run using source()
par(mfrow=c(1,2),mar=c(3,3,3,3))
#Panel 1
boxplot(main="(1) Proportion of Time Worked\nSpent Communicating",loball5ciA_la1$cprop[loball5ciA_la1$type=="contract"],
        loball5ciA_la1$cprop[loball5ciA_la1$type=="inhouse"],names=c("Contract","In-House"))
#Panel 2
boxplot(main="(2) Hours Spent Communicating",winsor1(loball5ciA_la1$hours[loball5ciA_la1$type=="contract"]*loball5ciA_la1$cprop[loball5ciA_la1$type=="contract"]),
        winsor1(loball5ciA_la1$hours[loball5ciA_la1$type=="inhouse"]*loball5ciA_la1$cprop[loball5ciA_la1$type=="inhouse"]),names=c("Contract","In-House"))


loball5ciA_la2<-loball5ciA %>%
  filter(Rcomparable_total_amounts_cleaned_exp>0) %>%
  group_by(lobname_cleaned,session,type) %>%
  summarise(.groups="keep",hours=sum(hours),communicating=sum(communicating),other=sum(other),cprop=sum(communicating)/sum(hours))


write.table(data.frame(Description=c("","Figure A8: Proportion of Time Worked and Total Hours Worked by Communicating with Public
Officials, Lobbyist Level")),row.names=F,col.names=F,quote=F)

png("output/PSRM_figA8.png",width=8,height = 4,units="in",res=300)
  par(mfrow=c(1,2),mar=c(3,3,3,3))
  #Panel 1
  boxplot(main="(1) Proportion of Time Worked\nSpent Communicating",loball5ciA_la2$cprop[loball5ciA_la2$type=="contract"],
          loball5ciA_la2$cprop[loball5ciA_la2$type=="inhouse"],names=c("Contract","In-House"))
  #Panel 2
  boxplot(main="(2) Hours Spent Communicating",winsor1(loball5ciA_la2$hours[loball5ciA_la2$type=="contract"]*loball5ciA_la2$cprop[loball5ciA_la2$type=="contract"]),
          winsor1(loball5ciA_la2$hours[loball5ciA_la2$type=="inhouse"]*loball5ciA_la2$cprop[loball5ciA_la2$type=="inhouse"]),names=c("Contract","In-House"))
dev.off()

write.table(data.frame(Description=c("(N.B. The file for this figure is saved as 'PSRM_figA8.png' in the 'output' folder.)")),row.names=F,col.names=F,quote=F)

#Include code again so that Figure A8 also shown in R if code file is run using source()
par(mfrow=c(1,2),mar=c(3,3,3,3))
#Panel 1
boxplot(main="(1) Proportion of Time Worked\nSpent Communicating",loball5ciA_la2$cprop[loball5ciA_la2$type=="contract"],
        loball5ciA_la2$cprop[loball5ciA_la2$type=="inhouse"],names=c("Contract","In-House"))
#Panel 2
boxplot(main="(2) Hours Spent Communicating",winsor1(loball5ciA_la2$hours[loball5ciA_la2$type=="contract"]*loball5ciA_la2$cprop[loball5ciA_la2$type=="contract"]),
        winsor1(loball5ciA_la2$hours[loball5ciA_la2$type=="inhouse"]*loball5ciA_la2$cprop[loball5ciA_la2$type=="inhouse"]),names=c("Contract","In-House"))


write.table(data.frame(Description=c("","Footnote results on median of the total number of hours worked in a given biennium:")),,row.names=F,col.names=F,quote=F)
print("In-House")
print("median(loball5ciA_la2$hours[loball5ciA_la2$type=='inhouse'])")
print(median(loball5ciA_la2$hours[loball5ciA_la2$type=="inhouse"]))

print("Contract")
print("median(loball5ciA_la2$hours[loball5ciA_la2$type=='contract'])")
print(median(loball5ciA_la2$hours[loball5ciA_la2$type=="contract"]))

#Prepare data for Table A5
loball5ciA$nlob<-loball5ciA$inhouse_halfyear_count+loball5ciA$contract_halfyear_count
loball5ciA<-loball5ciA %>%
  group_by(principal_cleaned,session) %>%
  mutate(principal_biennium_amount=sum(Rcomparable_total_amounts_cleaned_exp,na.rm=T))
loball5ciA_a<-loball5ciA %>%
  filter(principal_biennium_amount>0) %>%
  group_by(principal_cleaned,halfyear,halfyear2,session,both_halfyear_count1,contract_halfyear_count1) %>%
  summarise(.groups="keep",hours_agg=sum(hours),
            lobbying_top_pct_legprop_all=unique(lobbying_top_pct_legprop_all),
            lobbying_top_pct_budget_bill_all=unique(lobbying_top_pct_budget_bill_all),
            lobbying_top_pct_rulemaking_all=unique(lobbying_top_pct_rulemaking_all),
            lobbying_top_pct_nonum_all=unique(lobbying_top_pct_nonum_all),
            inhouse_halfyear_count=unique(inhouse_halfyear_count),
            contract_halfyear_count=unique(contract_halfyear_count),
            nlob=unique(nlob)
            ) %>%
  group_by(principal_cleaned,halfyear) %>%
  mutate(lobbying_top_hours_legprop_all=lobbying_top_pct_legprop_all*hours_agg,
         lobbying_top_hours_budget_bill_all=lobbying_top_pct_budget_bill_all*hours_agg,
         lobbying_top_hours_rulemaking_all=lobbying_top_pct_rulemaking_all*hours_agg,
         lobbying_top_hours_nonum_all=lobbying_top_pct_nonum_all*hours_agg,
         nlob=unique(nlob)
         )

m1<-lm(lobbying_top_pct_nonum_all~contract_halfyear_count1+both_halfyear_count1+hours_agg+nlob+as.factor(session)+as.factor(halfyear2),data=loball5ciA_a)

write.table(data.frame(Description=c("","Table A5: Relationship Between Clients Hiring Contract Lobbyist and Lobbying on Early Legis-
lation and Rules")),row.names=F,col.names=F,quote=F)

#stargazer(m1,type="latex",dep.var.labels=c("Percentage of Time Clients' Lobbyists Worked on Early Legislation / Rules"),covariate.labels = c("At Least One Contract Lobbyist","Both Types of Lobbyist","Total Hours","Number of Lobbyists","Session 2 ('07-'08)","Session 3 ('09-'10)","Session 4 ('11-'12) ","Session 5 ('13-'14)","Session 6 ('15-'16)","Session 7 ('17-'18)","Half-Year 2 (Jul-Dec, Year 1)","Half-Year 3 (Jan-Jun, Year 2)","Half-Year 4 (Jul-Dec, Year 2)"))
tab_a5<-stargazer(m1,type="latex",dep.var.labels=c("Percentage of Time Clients' Lobbyists Worked on Early Legislation / Rules"),covariate.labels = c("At Least One Contract Lobbyist","Both Types of Lobbyist","Total Hours","Number of Lobbyists","Session 2 ('07-'08)","Session 3 ('09-'10)","Session 4 ('11-'12) ","Session 5 ('13-'14)","Session 6 ('15-'16)","Session 7 ('17-'18)","Half-Year 2 (Jul-Dec, Year 1)","Half-Year 3 (Jan-Jun, Year 2)","Half-Year 4 (Jul-Dec, Year 2)"))

write(tab_a5[grep("begin\\{tabular",tab_a5):grep("end\\{tabular",tab_a5)],"output/PSRM_tabA5.tex")
write.table(data.frame(Description=c("(N.B. The file for this table is saved as 'PSRM_tabA5.tex' in the 'output' folder.)")),row.names=F,col.names=F,quote=F)


# % Table created by stargazer v.5.2.3 by Marek Hlavac, Social Policy Institute. E-mail: marek.hlavac at gmail.com
# % Date and time: Tue, Jul 23, 2024 - 17:53:18
# \begin{table}[!htbp] \centering 
# \caption{} 
# \label{} 
# \begin{tabular}{@{\extracolsep{5pt}}lc} 
# \\[-1.8ex]\hline 
# \hline \\[-1.8ex] 
# & \multicolumn{1}{c}{\textit{Dependent variable:}} \\ 
# \cline{2-2} 
# \\[-1.8ex] & Percentage of Time Clients' Lobbyists Worked on Early Legislation / Rules \\ 
# \hline \\[-1.8ex] 
#  At Least One Contract Lobbyist & 5.232$^{***}$ \\ 
#   & (0.656) \\ 
#   & \\ 
#  Both Types of Lobbyist & $-$0.203 \\ 
#   & (0.784) \\ 
#   & \\ 
#  Total Hours & $-$0.006$^{***}$ \\ 
#   & (0.001) \\ 
#   & \\ 
#  Number of Lobbyists & 0.844$^{***}$ \\ 
#   & (0.180) \\ 
#   & \\ 
#  Session 2 ('07-'08) & 3.821$^{***}$ \\ 
#   & (0.985) \\ 
#   & \\ 
#  Session 3 ('09-'10) & 3.367$^{***}$ \\ 
#   & (0.983) \\ 
#   & \\ 
#  Session 4 ('11-'12)  & 7.252$^{***}$ \\ 
#   & (0.988) \\ 
#   & \\ 
#  Session 5 ('13-'14) & 3.924$^{***}$ \\ 
#   & (0.996) \\ 
#   & \\ 
#  Session 6 ('15-'16) & 2.235$^{**}$ \\ 
#   & (0.981) \\ 
#   & \\ 
#  Session 7 ('17-'18) & 1.915$^{*}$ \\ 
#   & (0.985) \\ 
#   & \\ 
#  Half-Year 2 (Jul-Dec, Year 1) & $-$1.556$^{**}$ \\ 
#   & (0.754) \\ 
#   & \\ 
#  Half-Year 3 (Jan-Jun, Year 2) & $-$8.598$^{***}$ \\ 
#   & (0.749) \\ 
#   & \\ 
#  Half-Year 4 (Jul-Dec, Year 2) & 10.240$^{***}$ \\ 
#   & (0.755) \\ 
#   & \\ 
#  Constant & 23.656$^{***}$ \\ 
#   & (1.009) \\ 
#   & \\ 
# \hline \\[-1.8ex] 
# Observations & 19,508 \\ 
# R$^{2}$ & 0.045 \\ 
# Adjusted R$^{2}$ & 0.045 \\ 
# Residual Std. Error & 36.672 (df = 19494) \\ 
# F Statistic & 71.195$^{***}$ (df = 13; 19494) \\ 
# \hline 
# \hline \\[-1.8ex] 
# \textit{Note:}  & \multicolumn{1}{r}{$^{*}$p$<$0.1; $^{**}$p$<$0.05; $^{***}$p$<$0.01} \\ 
# \end{tabular} 
# \end{table} 

write.table(data.frame(Description=c("","Median percentage of effort devoted to 'Topics Not Yet Assigned A Bill Or Rule':")),,row.names=F,col.names=F,quote=F)
print("median(loball5ciA_a$lobbying_top_pct_nonum_all)")
print(median(loball5ciA_a$lobbying_top_pct_nonum_all))


# "N.B. Due to name changes of organizations and cleaning of organization names, 
# two organizations (principal_cleaned) have multiple filings for the same disclosure period:
# 'Wisconsin State Fire Chiefs Association' (in 2017-2018) and 'Wisconsin Apartment Association (formerly' (in 2011-2012). 
# If these observations are excluded, results are substantively unchanged.")),row.names=F,col.names=F,quote=F)
# m1b<-lm(lobbying_top_pct_nonum_all~contract_halfyear_count1+both_halfyear_count1+hours_agg+nlob+as.factor(session)+as.factor(halfyear2),data=loball5ciA_a[which(duplicated(loball5ciA_a[,c("principal_cleaned","halfyear")],fromLast = T)==F&duplicated(loball5ciA_a[,c("principal_cleaned","halfyear")],fromLast = F)==F),])

# stargazer(m1b,type="latex",dep.var.labels=c("Percentage of Time Clients' Lobbyists Worked on Early Legislation / Rules"),covariate.labels = c("At Least One Contract Lobbyist","Both Types of Lobbyist","Total Hours","Number of Lobbyists","Session 2 ('07-'08)","Session 3 ('09-'10)","Session 4 ('11-'12) ","Session 5 ('13-'14)","Session 6 ('15-'16)","Session 7 ('17-'18)","Half-Year 2 (Jul-Dec, Year 1)","Half-Year 3 (Jan-Jun, Year 2)","Half-Year 4 (Jul-Dec, Year 2)"))
# % Table created by stargazer v.5.2.3 by Marek Hlavac, Social Policy Institute. E-mail: marek.hlavac at gmail.com
# % Date and time: Fri, Aug 09, 2024 - 20:26:33
# \begin{table}[!htbp] \centering 
# \caption{} 
# \label{} 
# \begin{tabular}{@{\extracolsep{5pt}}lc} 
# \\[-1.8ex]\hline 
# \hline \\[-1.8ex] 
# & \multicolumn{1}{c}{\textit{Dependent variable:}} \\ 
# \cline{2-2} 
# \\[-1.8ex] & Percentage of Time Clients' Lobbyists Worked on Early Legislation / Rules \\ 
# \hline \\[-1.8ex] 
#  At Least One Contract Lobbyist & 5.242$^{***}$ \\ 
#   & (0.657) \\ 
#   & \\ 
#  Both Types of Lobbyist & $-$0.152 \\ 
#   & (0.785) \\ 
#   & \\ 
#  Total Hours & $-$0.006$^{***}$ \\ 
#   & (0.001) \\ 
#   & \\ 
#  Number of Lobbyists & 0.837$^{***}$ \\ 
#   & (0.180) \\ 
#   & \\ 
#  Session 2 ('07-'08) & 3.820$^{***}$ \\ 
#   & (0.985) \\ 
#   & \\ 
#  Session 3 ('09-'10) & 3.367$^{***}$ \\ 
#   & (0.984) \\ 
#   & \\ 
#  Session 4 ('11-'12)  & 7.294$^{***}$ \\ 
#   & (0.989) \\ 
#   & \\ 
#  Session 5 ('13-'14) & 3.921$^{***}$ \\ 
#   & (0.996) \\ 
#   & \\ 
#  Session 6 ('15-'16) & 2.233$^{**}$ \\ 
#   & (0.982) \\ 
#   & \\ 
#  Session 7 ('17-'18) & 1.978$^{**}$ \\ 
#   & (0.986) \\ 
#   & \\ 
#  Half-Year 2 (Jul-Dec, Year 1) & $-$1.548$^{**}$ \\ 
#   & (0.755) \\ 
#   & \\ 
#  Half-Year 3 (Jan-Jun, Year 2) & $-$8.603$^{***}$ \\ 
#   & (0.750) \\ 
#   & \\ 
#  Half-Year 4 (Jul-Dec, Year 2) & 10.234$^{***}$ \\ 
#   & (0.755) \\ 
#   & \\ 
#  Constant & 23.655$^{***}$ \\ 
#   & (1.009) \\ 
#   & \\ 
# \hline \\[-1.8ex] 
# Observations & 19,494 \\ 
# R$^{2}$ & 0.045 \\ 
# Adjusted R$^{2}$ & 0.045 \\ 
# Residual Std. Error & 36.679 (df = 19480) \\ 
# F Statistic & 71.200$^{***}$ (df = 13; 19480) \\ 
# \hline 
# \hline \\[-1.8ex] 
# \textit{Note:}  & \multicolumn{1}{r}{$^{*}$p$<$0.1; $^{**}$p$<$0.05; $^{***}$p$<$0.01} \\ 
# \end{tabular} 
# \end{table} 


